﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Xml;

namespace VarMap.Source_Files
{
    class VarMapGenerator : IDataReader
    {
        private string _path;
        private string _name;

        public string FilePath
        {
            get
            {
                return _path;
            }
            set
            {
                _path = value == string.Empty ? Directory.GetCurrentDirectory() : value;               
            }
        }

        public string FileName
        {
            get
            {
                return _name;
            }
            set
            {
                if (value == string.Empty)
                {
                    throw new ToolException("001", "Source data file name cannot be empty");
                }
                _name = value;
            }
        }
        
        public DataSet GetData(string file, IList<string> sheetNames, IList<string> columns)
        {
            DataSet dSet = new DataSet();
            // string oleConnStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Password=\"{0}\";User ID={1};Data Source=\"{2}\";Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37", password, user, file);
            string oleConnStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Mode=Share Deny Write;Extended Properties='HDR=YES;';Jet OLEDB:Engine Type=37", file);
            
            string oleCmdStr = "";
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.Append("SELECT ");
            if (columns != null && columns.Count() > 0)
            {
                // StringBuilder strBuilder = new StringBuilder();
               // strBuilder.Append("SELECT ");
                for (int index = 0; index < columns.Count() - 1; index++)
                {
                    strBuilder.Append(string.Format("[{0}], ", columns[index]));                    
                }
                strBuilder.Append(string.Format("[{0}] ", columns[columns.Count() - 1]));               
            }
            else
            {
                strBuilder.Append("* ");
                //oleCmdStr = string.Format("SELECT * FROM [{0}$];", sheetName == string.Empty ? "Sheet1" : sheetName);
            }

            OleDbConnection oleConn = new OleDbConnection(oleConnStr);
            OleDbCommand oleCmd;
            OleDbDataAdapter oleAdapter;

            string tempSheetName;
            for (int index = 0; index < sheetNames.Count(); index++)
            {
                tempSheetName = sheetNames[index] == string.Empty ? "Sheet1" :sheetNames[index];
               // strBuilder.Append(string.Format("FROM [{0}$];", tempSheetName));
                oleCmdStr = string.Format("{0} FROM [{1}$];", strBuilder.ToString(), tempSheetName);

                oleCmd = new OleDbCommand(oleCmdStr, oleConn);
                oleAdapter = new OleDbDataAdapter();
                oleAdapter.TableMappings.Add("Table", tempSheetName);
                oleAdapter.SelectCommand = oleCmd;

                try
                {
                    oleConn.Open();
                    oleAdapter.SelectCommand = oleCmd;
                    oleAdapter.Fill(dSet);
                }
                catch (Exception err)
                {
                    throw new ToolException("111", string.Format("OLE failed to load the data for: {0}", err.Message));
                }
                finally
                {
                    oleConn.Close();
                }
            }

            return dSet;
        }
        
        public void ToXML(DataTable dataTable, string path, string set, string className, Dictionary<string, string> rootNodeAttributes, Dictionary<string, string> configInfo)
        {           
            XmlDocument xmlDoc = new XmlDocument();
            XmlDeclaration xmlDesc = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", null);
            xmlDoc.AppendChild(xmlDesc);
            XmlElement rootElement = xmlDoc.CreateElement("Varmap");

            foreach (string key in rootNodeAttributes.Keys)
            {
                rootElement.SetAttribute(key, rootNodeAttributes[key]);
            }

            XmlElement rootSection = xmlDoc.CreateElement("Section");
            rootSection.SetAttribute("dsc", "All functional tests");       
                      
            XmlElement snippet = CreateSnippet(xmlDoc, configInfo);

            XmlElement sectionSecond = xmlDoc.CreateElement("section");
            sectionSecond.SetAttribute("dsc", "Second");
            XmlElement sectionThird = xmlDoc.CreateElement("section");
            sectionThird.SetAttribute("dsc", "Third");
            sectionSecond.AppendChild(sectionThird);

            DataRow[] dRows;
            for (int priority = 0; priority < 5; priority++)
            {
                dRows = dataTable.Select("Priority = " + priority);
                if (dRows.Count() > 0)
                {
                    sectionThird.AppendChild(CreateVarSection(xmlDoc, dRows, set, className));                    
                }
            }

            rootSection.AppendChild(snippet);
            rootSection.AppendChild(sectionSecond);
            
            rootElement.AppendChild(rootSection);
            rootElement.AppendChild(CreateGrp(xmlDoc, set, "", "ALL", className));
            xmlDoc.AppendChild(rootElement);

            path = path == string.Empty ? Directory.GetCurrentDirectory() : path;
            xmlDoc.Save( path + "\\" + dataTable.TableName + ".xml");
            return;            
        }      

        private XmlElement CreateSnippet(XmlDocument xmlDoc, Dictionary<string, string> configInfo)
        {
            XmlElement snippet = xmlDoc.CreateElement("rec") ;
            snippet.SetAttribute("id", "ConfigInfo");

            XmlElement rec;
            foreach (string key in configInfo.Keys)
            {
                rec = xmlDoc.CreateElement("rec");
                rec.SetAttribute("key", key);
                rec.InnerText = configInfo[key];
                snippet.AppendChild(rec);
            }

            return snippet;
        }

        private XmlElement CreateVarSection(XmlDocument xmlDoc, DataRow[] dRows, string set, string className)
        {
            XmlElement varSection = xmlDoc.CreateElement("Section");
            varSection.SetAttribute("dsc", string.Format("P{0} Function Test, count: {1}", dRows[0]["Priority"].ToString(), dRows.Count()));
            XmlElement varNode;
            XmlElement fnc;
            XmlElement snipref = xmlDoc.CreateElement("snipref");
            snipref.SetAttribute("id", "ConfigInfo");

            for (int i = 0; i < dRows.Count(); i++)
            {
                varNode = xmlDoc.CreateElement("var");
                varNode.SetAttribute("set", set);
                varNode.SetAttribute("lvl", dRows[i]["Priority"].ToString());
                varNode.SetAttribute("vid", dRows[i]["ID"].ToString());
                varNode.SetAttribute("cls", className);
                // varNode.SetAttribute("dsc", dRows[i]["Description"].ToString());
                varNode.SetAttribute("dsc", dRows[i]["Title"].ToString());
                fnc = xmlDoc.CreateElement("fnc");
                fnc.InnerText = dRows[i]["Title"].ToString();

                varNode.AppendChild(snipref.Clone());
                varNode.AppendChild(fnc);
                varSection.AppendChild(varNode);
            }
            
            return varSection;
        }

        private XmlElement CreateGrp(XmlDocument xmlDoc, string set, string dsc, string gid, string cls)
        {
            XmlElement rootgrp = xmlDoc.CreateElement("grp");
            rootgrp.SetAttribute("dsc", dsc);
            rootgrp.SetAttribute("gid", gid);
            rootgrp.SetAttribute("cls", cls);

            XmlElement grp;
            XmlElement varref;

            for (int i = 0; i < 4; i++)
            {
                grp = xmlDoc.CreateElement("grp");
                grp.SetAttribute("dsc", string.Format("P{0} Functional Test", i));
                grp.SetAttribute("gid", string.Format("P{0}", i));
                varref = xmlDoc.CreateElement("varref");
                varref.SetAttribute("set", set);
                varref.SetAttribute("lvl", i.ToString());

                grp.AppendChild(varref);
                rootgrp.AppendChild(grp);
            }

            return rootgrp;
        }
    }
}
